package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.LinkedHashMap;
import java.util.Properties;
import java.util.TimeZone;
import java.util.logging.Level;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.acct.Doc;
import org.compiere.apps.ADialog;
import org.compiere.model.MAccount;
import org.compiere.model.MAcctSchema;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MDocType;
import org.compiere.model.MPayment;
import org.compiere.model.MRefList;
import org.compiere.model.MhrmbonusLine;
import org.compiere.model.Mhrmbusinesstrip;
import org.compiere.model.Mhrmillnessline;
import org.compiere.model.Mhrmintercheckoff;
import org.compiere.model.Mhrmmaterialaid;
import org.compiere.model.Mhrmmaterialaidline;
import org.compiere.model.Mhrmovertimeline;
import org.compiere.model.Mhrmreviewline;
import org.compiere.model.Mhrmsalaryemployee;
import org.compiere.model.Mhrmsettax;
import org.compiere.model.Mhrmtax;
import org.compiere.model.Mhrmvacationline;
import org.compiere.model.X_C_DocType;
import org.compiere.model.X_C_Payment;
import org.compiere.model.X_hrm_absence;
import org.compiere.model.X_hrm_bonusLine;
import org.compiere.model.X_hrm_intercheckoff;
import org.compiere.model.X_hrm_materialaid;
import org.compiere.model.X_hrm_order;
import org.compiere.model.X_hrm_salaryemployee;
import org.compiere.model.X_hrm_set_tax;
import org.compiere.model.X_hrm_taxexemptions;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Language;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Employee;
import org.joda.time.DateTime;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.hrm.TaxCompute;
import extend.org.compiere.utils.Util;

public class GeneratePayrollList extends SvrProcess {
	
	/** Current context					*/
	private Properties m_ctx;
	/** */
	private String path = "";
	/** */
	private int bpartnerID;
	/**	 */
	private ProcessInfo pi;
	/** Get month and year */
	GregorianCalendar calendar = new GregorianCalendar(TimeZone.getTimeZone("GMT"));
	SimpleDateFormat dateFormat = new SimpleDateFormat("ddMMyyyy");
	/** */
	Timestamp startDate;
	/** */
	Timestamp endDate;
	/** */
	Timestamp dateFrom;
	/** The target date */
	private static DateTime CONST_DATE = new DateTime("2012-10-01");
	
	/** The target date */
	private DateTime targetDate = new DateTime();
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (GeneratePayrollList.class);

	@Override
	protected void prepare() {
		
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
				String name = para[i].getParameterName();
				if (name.equals("C_BPartner_ID"))
					bpartnerID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
				else if (name.compareTo("DateFrom") == 0){
					startDate = (Timestamp) para[i].getParameter();
					endDate = (Timestamp) para[i].getParameter_To();
				}
				else if (name.toLowerCase().endsWith("directory"))
					path = (String)para[i].getParameter() + System.getProperty("file.separator");
				else
					log.log(Level.SEVERE, "Unknown Parameter: " + name);			
		}	
	}

	@Override
	protected String doIt() throws Exception {
		
		// Types of cells for
		Label label;
		Number number;
		Formula formula;
		BigDecimal amount = new BigDecimal(0);
		BigDecimal AMOUNT_TOTAL = new BigDecimal(0);
		
		// Define employee identification
		MBPartner bp = new MBPartner(getCtx(), bpartnerID, get_TrxName());
		// set of employee benefits
		LinkedHashMap<String, Integer> taxList = TaxCompute.getSetTaxExemptions(bpartnerID);
			
		// There are checks for a specified pattern
		String templateName = " Расчетный_лист_" + bp.getLastName() + "_за_период_с_" + dateFormat.format(startDate) + "_по_" + dateFormat.format(endDate);
		String startMonth = "";
		String endMonth = "";
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate") + " " + templateName);
		
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("Payroll print").append(fileExtension);
		String sampleTemp = Util.localFilePath + "AdempiereTemp";
		
		// 
		File templateCopy = new File(fullPath.toString());
		File sampleCopy = new File(sampleTemp);
		File temporaryFile = entry.getFile(Util.localFilePath + templateName);
		File temporarySample = entry.getFile(Util.localFilePath + templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		// for sample
		Workbook sample = null;
		WritableWorkbook samCopy = null;
		WritableSheet sheet_1 = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
			 // for sample
			 sample = Workbook.getWorkbook(temporarySample);
			 samCopy = Workbook.createWorkbook(sampleCopy, sample);
			 sheet_1 = samCopy.getSheet(0);
			 samCopy.write();
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
			return "Error tableBook.";
		}
		 					
       // Title style cell
       WritableFont fontTitle = new WritableFont (WritableFont.TAHOMA, 10, WritableFont.BOLD);
       WritableCellFormat titleStyle = new WritableCellFormat(fontTitle);
       titleStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       titleStyle.setAlignment(Alignment.CENTRE);
       titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       // Main style cell
       WritableCellFormat mainStyle = new WritableCellFormat();
	   mainStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	   mainStyle.setAlignment(Alignment.LEFT );
	   mainStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	   mainStyle.setWrap(true);
	   
	   // Standard style cell
	   WritableCellFormat borderStyle = new WritableCellFormat();
	   borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	   borderStyle.setAlignment(Alignment.CENTRE);
	   borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	   borderStyle.setWrap(true);
	   
	   //
	   WritableCellFormat numberStyle = new WritableCellFormat(NumberFormats.THOUSANDS_FLOAT);
	   numberStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	   numberStyle.setAlignment(Alignment.RIGHT);
	   numberStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	   numberStyle.setWrap(true);
		   
	   //
	   DateFormat customDateFormat = new DateFormat ("dd.MM.yyyy");
	   WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);
	   dateFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	   dateFormat.setAlignment(Alignment.CENTRE);
	   dateFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
	   
	   /** Body */	   
	   // Title
	    X_HR_Employee he = X_HR_Employee.getHR_Employee(bp.getC_BPartner_ID(),null);
	   if (he.getC_BPartner_ID() == 0)
		   throw new AdempiereException ("Info about employee was empty");
	  
	   int cntMonth = Util.getMonth(startDate, endDate)+1;
	   
	   targetDate = new DateTime(startDate);
	   if (targetDate.getDayOfMonth() <= 1)
		   calendar.setTime(targetDate.plusDays(5).toDate());
	   else
		   calendar.setTime(targetDate.toDate());
	   
	   ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
       ExcelCell cellStart_1 =  Util.getCellStart(tableBook,">>>");
	   ExcelCell capS =  Util.getCellStart(tableBook,">>>>");
	   ExcelCell capE =  Util.getCellStart(tableBook,"<<<<");
	   // Clear >>>> <<<< and >>>
	   label = new Label(cellStart_1.getC(), cellStart_1.getR(), " ");
	   sheet.addCell(label);
	   label = new Label(capS.getC(), capS.getR(), " ");
	   sheet.addCell(label);
	   label = new Label(capE.getC(), capE.getR(), " ");
	   sheet.addCell(label);
	   
	   double sumMZP =0.;
	   int cntRow = capS.getR()+(capE.getR()-capS.getR())+4;
	   int i=0;
	   for(int m = 0; m < cntMonth; m++){
		   
		   calendar.set(Calendar.DAY_OF_MONTH, 0);
		   dateFrom = new Timestamp(calendar.getTime().getTime());
		   targetDate = new DateTime(dateFrom.getTime()); 
		   
		   int row = cellStart_1.getR();
		   if(m > 0){
/*			   if(m == 2)
				   cntRow = cntRow + 2;
*/			   copyTo(sheet_1, sheet, capS, capE, cntRow);
			   row = cntRow-2;
			   cntRow = cntRow+ (capE.getR()-capS.getR())+4;
		   }
		   
		   i=0;		   
		   
		   // Tax declare
		   BigDecimal taxMZP = null;
		   taxMZP = Mhrmsettax.getRateValue(X_hrm_set_tax.HRM_TAXTYPE_MZP, dateFrom);
		   if(targetDate.getMonthOfYear() == 1)
			   sumMZP = 0.;
		   sumMZP = sumMZP + taxMZP.doubleValue();

		   /** Take date */
		   String month = Util.getMonthName(dateFrom, Env.getAD_Language(getCtx()), false);
		   sheet.mergeCells(1, row+2, 13, row+2);
		   sheet.setRowView(row+2, 700);
		   label = new Label(1, row+2, sheet.getCell(1, row+2).getContents()+" "+month + " " + targetDate.getYear() + " г.", titleStyle);
		   sheet.addCell(label);
		   /** Employee data */
		   sheet.mergeCells(2, row+3, 5, row+3);
		   sheet.setRowView(row+3, 700);
		   label = new Label(2, row+3, Env.getContext(m_ctx, "#AD_Org_Name"), borderStyle);
		   sheet.addCell(label);
		   sheet.mergeCells(2, row+4, 5, row+4);
		   sheet.setRowView(row+4, 350);
		   label = new Label(2, row+4, bp.getName(), borderStyle);
		   sheet.addCell(label);
		   sheet.mergeCells(2, row+5, 5, row+5);
		   sheet.setRowView(row+5, 350);
		   label = new Label(2, row+5, Integer.toString(bp.gethrm_tableNumber()), borderStyle);
		   sheet.addCell(label);
		   sheet.mergeCells(6, row+3, 7, row+3);
		   sheet.mergeCells(8, row+3, 13, row+3);
		   label = new Label(8, row+3, he.getHR_Department().getName(), borderStyle);
		   sheet.addCell(label);
		   sheet.mergeCells(6, row+4, 7, row+4);
		   sheet.mergeCells(8, row+4, 13, row+4);
		   label = new Label(8, row+4, he.getHR_Job().getName(), borderStyle);
		   sheet.addCell(label);
		   sheet.mergeCells(6, row+5, 10, row+5);
		   sheet.mergeCells(11, row+5, 13, row+5);		   
		   BigDecimal EMPLOYEESUM = getEmployeeSum(bp.getC_BPartner_ID(), dateFrom, "SALARY");
		   number = new Number(11, row+5, EMPLOYEESUM.doubleValue(), numberStyle);
		   sheet.addCell(number);
		   // С нач.года: начисл.дох.
		   AMOUNT_TOTAL = getEmployeeSumTotal(bpartnerID, dateFrom);
		   number = new Number(2, row+6, AMOUNT_TOTAL.doubleValue(), numberStyle);
		   sheet.addCell(number);
		   // Необл.дох
		   sheet.mergeCells(3, row+6, 4, row+6);
		   BigDecimal MAXIPN = new BigDecimal(0);
		   if(taxList.get(X_hrm_taxexemptions.HRM_TAXTYPE_55MZP) != null){
			   MAXIPN = Mhrmtax.getIPNofYear(bpartnerID, taxList.get(X_hrm_taxexemptions.HRM_TAXTYPE_55MZP), dateFrom);
			   number = new Number(2, row+6, MAXIPN.doubleValue(), numberStyle);
			   sheet.addCell(number);
		   }
		   // ОПВ
		   sheet.mergeCells(7, row+6, 8, row+6);
		   BigDecimal OPV = getSumTax(bpartnerID, dateFrom, "OPV");
		   number = new Number(7, row+6, OPV.doubleValue(), numberStyle);
		   sheet.addCell(number);
		   // Выч.
		   sheet.mergeCells(10, row+6, 11, row+6);
		   BigDecimal RELIEF = getSumTax(bpartnerID, dateFrom, "RELIEF");
		   number = new Number(10, row+6, RELIEF.doubleValue(), numberStyle);
		   sheet.addCell(number);
		   // ИПН
		   BigDecimal IPN = getSumTax(bpartnerID, dateFrom, "IPN");
		   number = new Number(13, row+6, IPN.doubleValue(), numberStyle);
		   sheet.addCell(number);
		   //////
		   sheet.mergeCells(1, row+7, 13, row+7);
		   // Вид
		   sheet.mergeCells(6, row+8, 8, row+8);
		   // Период
		   sheet.mergeCells(9, row+8, 11, row+8);
		   // Сумма
		   sheet.mergeCells(12, row+8, 13, row+8);

		   /** Fill other cells */
		   // Salary
		   Mhrmsalaryemployee mhrmsle = Mhrmsalaryemployee.getSalary(bp.getC_BPartner_ID(), dateFrom);
		   if(mhrmsle == null)
			   	throw new AdempiereException("Не установлен оклад на данный период");
		   
		   sheet.mergeCells(1, row+9, 5, row+9);
		   sheet.mergeCells(6, row+9, 13, row+9);
		   sheet.mergeCells(1, row+15, 5, row+15);
		   // // Долг за организацией на начало месяца
		   sheet.mergeCells(1, row+19, 4, row+19);
		   /** Удержано */
		   // ИПН исчисленный
		   sheet.mergeCells(6, row+10, 9, row+10);
		   sheet.mergeCells(10, row+10, 11, row+10);
		   sheet.mergeCells(12, row+10, 13, row+10);
		   
		   // Last line
		   sheet.mergeCells(1, row+20, 13, row+20);
		   // Начисление оклада
		   label = new Label(cellStart.getC(), row+10, "1. " + MRefList.getListName(getCtx(), X_hrm_salaryemployee.HRM_SALARYTYPE_AD_Reference_ID, mhrmsle.gethrm_salarytype()), mainStyle);
		   sheet.addCell(label);
		   label = new Label(cellStart.getC()+1, row+10, month + " " + targetDate.getYear() + " г.", mainStyle);
		   sheet.addCell(label);
		   int days = Mhrmsalaryemployee.getSalaryDays(bp.getC_BPartner_ID(), dateFrom);
		   number = new Number(cellStart.getC()+2, row+10, days, borderStyle);
		   sheet.addCell(number);
		   int hour = Mhrmsalaryemployee.getSalaryHours(bp.getC_BPartner_ID(), dateFrom);
		   number = new Number(cellStart.getC()+3, row+10, hour, borderStyle);
		   sheet.addCell(number);
		   if (dateFrom.getTime() < CONST_DATE.getMillis()){			   
			   amount = getEmployeeSum(bp.getC_BPartner_ID(), dateFrom, "HRM_TOTAL");
		   }else{
			   amount = getEmployeeSum(bp.getC_BPartner_ID(), dateFrom, "AMOUNT");
		   }
		   number = new Number(cellStart.getC()+4, row+10, amount.doubleValue(), numberStyle);
		   sheet.addCell(number);
			
		   //Bonus
		   MhrmbonusLine[] mhrmbl = MhrmbonusLine.getBonusArray(bp.getC_BPartner_ID(),dateFrom);
		   for (int k=0; k < mhrmbl.length; k++){
		     if (mhrmbl[k] != null){
			   if (mhrmbl[k].get_ID() != 0 && mhrmbl[k].gethrm_amountbonus().doubleValue() != 0.){
				   i++; 
				   label = new Label(cellStart.getC(), row+10+i, Integer.toString(i+1) + ". " + MRefList.getListName(getCtx(), X_hrm_bonusLine.HRM_BONUSTYPE_AD_Reference_ID, mhrmbl[k].gethrm_bonustype()), mainStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+4, row+10+i, mhrmbl[k].gethrm_total().doubleValue(), numberStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(6, row+10+i).getContents(), mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+9, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+11, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   sheet.mergeCells(6, row+10+i, 9, row+10+i);
				   sheet.mergeCells(10, row+10+i, 11, row+10+i);
				   sheet.mergeCells(12, row+10+i, 13, row+10+i);
			   }
		     }
		   }
		   
		   // Material aid
		   Mhrmmaterialaidline[] mhrmaid = Mhrmmaterialaidline.getAids(bp.getC_BPartner_ID(), dateFrom);
		   for (int k=0; k < mhrmaid.length; k++){
		     if (mhrmaid[k] != null){
			   if (mhrmaid[k].get_ID() != 0){
				   i++;
				   if (i > 2){
					   // shift the basement
					   sheet.insertRow(row+10 + i);
					   cntRow++;
				   }
				   Mhrmmaterialaid aidtype = new Mhrmmaterialaid(Env.getCtx(), mhrmaid[k].gethrm_materialaid_ID(), null); 
				   label = new Label(cellStart.getC(), row+10+i, Integer.toString(i+1) + ". " + MRefList.getListName(getCtx(), X_hrm_materialaid.HRM_MATERIALAIDTYPE_AD_Reference_ID, aidtype.gethrm_materialaidtype()), mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+1, row+10+i, " ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+2, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+3, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+4, row+10+i, mhrmaid[k].gethrm_total().doubleValue(), numberStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(cellStart.getC()+5, row+10+i).getContents()+" ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(6, row+10+i).getContents(), mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+9, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+11, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   sheet.mergeCells(6, row+10+i, 9, row+10+i);
				   sheet.mergeCells(10, row+10+i, 11, row+10+i);
				   sheet.mergeCells(12, row+10+i, 13, row+10+i);
			   }
		     }
		   }
		   
		   // Materialaid taxable
		   Mhrmvacationline[] mhrvma = Mhrmvacationline.getVacLineArray(bp.getC_BPartner_ID(), dateFrom);
		   for (int k=0; k < mhrvma.length; k++){
		     if (mhrvma[k] != null){
			   if (mhrvma[k].get_ID() != 0){
				   i++;
				   if (i > 2){
					   // shift the basement
					   sheet.insertRow(row+10 + i);
					   cntRow++;
				   }
				   label = new Label(cellStart.getC(), row+10+i, Integer.toString(i+1) + ". Мат. помощь, обл-мая", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+1, row+10+i, Util.getMonthName(dateFrom, Language.AD_Language_ru_RU, false), mainStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+2, row+10+i, 0, borderStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+3, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+4, row+10+i, mhrvma[k].gethrm_materialaid().doubleValue(), numberStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(cellStart.getC()+5, row+10+i).getContents()+" ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(6, row+10+i).getContents(), mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+9, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+11, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   sheet.mergeCells(6, row+10+i, 9, row+10+i);
				   sheet.mergeCells(10, row+10+i, 11, row+10+i);
				   sheet.mergeCells(12, row+10+i, 13, row+10+i);
			   	}
			  }							
		   }
		   
		   // Vacation
		   Mhrmvacationline[] mhrmvl = Mhrmvacationline.getVacLineArray(bp.getC_BPartner_ID(), dateFrom);
		   for (int k=0; k < mhrmvl.length; k++){
		     if (mhrmvl[k] != null){
			   if (mhrmvl[k].get_ID() != 0){
				   i++;
				   if (i > 2){
					   // shift the basement
					   sheet.insertRow(row+10 + i);
					   cntRow++;
				   }
				   label = new Label(cellStart.getC(), row+10+i, Integer.toString(i+1) + ". " + MRefList.getListName(getCtx(), X_hrm_order.HRM_WORKLEAVETYPE_AD_Reference_ID, mhrmvl[k].gethrm_workleavetype()), mainStyle);
				   sheet.addCell(label);
				   //set dates
				  
				   startMonth = Util.getMonthName(mhrmvl[k].getStartDate(), Env.getAD_Language(getCtx()), true);
				   startMonth = " c " + new DateTime(mhrmvl[k].getStartDate().getTime()).getDayOfMonth() + " " + startMonth;
				   
				   endMonth = Util.getMonthName(mhrmvl[k].getEndDate(), Env.getAD_Language(getCtx()), true);
				   endMonth = startMonth + " по " + new DateTime(mhrmvl[k].getEndDate().getTime()).getDayOfMonth() + " " + endMonth + " " + new DateTime(mhrmvl[k].getEndDate().getTime()).getYear(); 
				   label = new Label(cellStart.getC()+1, row+10+i, endMonth, mainStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+2, row+10+i, mhrmvl[k].gethrm_days(), borderStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+3, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+4, row+10+i, mhrmvl[k].gethrm_total().doubleValue(), numberStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(cellStart.getC()+5, row+10+i).getContents()+" ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(6, row+10+i).getContents(), mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+9, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+11, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   sheet.mergeCells(6, row+10+i, 9, row+10+i);
				   sheet.mergeCells(10, row+10+i, 11, row+10+i);
				   sheet.mergeCells(12, row+10+i, 13, row+10+i);
			   	}
			  }							
		   }
			
		   // Illness
		   Mhrmillnessline[] mhrmill = Mhrmillnessline.getIllLineArray(bp.getC_BPartner_ID(), dateFrom);
		   for (int k=0; k < mhrmill.length; k++){
			if (mhrmill[k] != null){
			   if (mhrmill[k].get_ID() != 0){
				   i++;
				   if (i > 2){
					   // shift the basement
					   sheet.insertRow(row+10 + i);
					   cntRow++;
				   }
				   label = new Label(cellStart.getC(), row+10+i, Integer.toString(i+1) + ". " + MRefList.getListName(getCtx(), X_hrm_absence.HRM_ABSENCETYPE_AD_Reference_ID, mhrmill[k].gethrm_absenceType()), mainStyle);
				   sheet.addCell(label);
				   //set dates
				   startMonth = Util.getMonthName(mhrmill[k].getStartDate(), Env.getAD_Language(getCtx()), true);
				   startMonth = " c " + new DateTime(mhrmill[k].getStartDate().getTime()).getDayOfMonth() + " " + startMonth;
				   endMonth = Util.getMonthName(mhrmill[k].getEndDate(), Env.getAD_Language(getCtx()), true);
				   endMonth = startMonth + " по " + new DateTime(mhrmill[k].getEndDate().getTime()).getDayOfMonth() + " " + endMonth + " " + new DateTime(mhrmill[k].getEndDate().getTime()).getYear(); 
				   label = new Label(cellStart.getC()+1, row+10+i, endMonth, mainStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+2, row+10+i, mhrmill[k].gethrm_days(), borderStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+3, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+4, row+10+i, mhrmill[k].gethrm_total().doubleValue(), numberStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(cellStart.getC()+5, row+10+i).getContents()+" ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(6, row+10+i).getContents(), mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+9, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+11, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   sheet.mergeCells(6, row+10+i, 9, row+10+i);
				   sheet.mergeCells(10, row+10+i, 11, row+10+i);
				   sheet.mergeCells(12, row+10+i, 13, row+10+i);
				   }
			   }
		   }
			
		   // Medical care
		   BigDecimal medicare = getEmployeeSum(bp.getC_BPartner_ID(), dateFrom, "MEDICARE");
		   if (medicare.doubleValue() != 0){
				   i++;
				   if (i > 2){
					   // shift the basement
					   sheet.insertRow(row+10 + i);
					   cntRow++;
				   }
				   label = new Label(cellStart.getC(), row+10+i, Integer.toString(i+1) + ".  Медстахование ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+1, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+2, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+3, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+4, row+10+i, medicare.doubleValue(), numberStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(cellStart.getC()+5, row+10+i).getContents()+" ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(6, row+10+i).getContents(), mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+9, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+11, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   sheet.mergeCells(6, row+10+i, 9, row+10+i);
				   sheet.mergeCells(10, row+10+i, 11, row+10+i);
				   sheet.mergeCells(12, row+10+i, 13, row+10+i);
		   }
		   
		   // Profit
		   BigDecimal profit = getEmployeeSum(bp.getC_BPartner_ID(), dateFrom, "PROFIT");
		   if (profit.doubleValue() != 0){
				   i++;
				   if (i > 2){
					   // shift the basement
					   sheet.insertRow(row+10 + i);
					   cntRow++;
				   }
				   label = new Label(cellStart.getC(), row+10+i, Integer.toString(i+1) + ".  Доп. доход ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+1, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+2, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+3, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+4, row+10+i, profit.doubleValue(), numberStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(cellStart.getC()+5, row+10+i).getContents()+" ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(6, row+10+i).getContents(), mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+9, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+11, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   sheet.mergeCells(6, row+10+i, 9, row+10+i);
				   sheet.mergeCells(10, row+10+i, 11, row+10+i);
				   sheet.mergeCells(12, row+10+i, 13, row+10+i);
		   }
			
		   // Review 
		   Mhrmreviewline[] mhrmrevline = Mhrmreviewline.getRevLineArray(bp.getC_BPartner_ID(), dateFrom);								
		   for (int k=0; k < mhrmrevline.length; k++){
			   if (mhrmrevline[k] != null){
				   if (mhrmrevline[k].get_ID() !=0){
				   i++;
				   if (i > 2){
					   // shift the basement
					   sheet.insertRow(row+10 + i);
					   cntRow++;
				   }
				   label = new Label(cellStart.getC(), row+10+i, Integer.toString(i+1) + ". "
						   + MRefList.getListName(getCtx(), X_hrm_order.HRM_COMPENSATIONTYPE_AD_Reference_ID, mhrmrevline[k].gethrm_compensationtype()), mainStyle);								
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+2, row+10+i, mhrmrevline[k].gethrm_days(), borderStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+3, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+4, row+10+i, mhrmrevline[k].gethrm_total().doubleValue(), numberStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(cellStart.getC()+5, row+10+i).getContents()+" ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(6, row+10+i).getContents(), mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+9, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+11, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   sheet.mergeCells(6, row+10+i, 9, row+10+i);
				   sheet.mergeCells(10, row+10+i, 11, row+10+i);
				   sheet.mergeCells(12, row+10+i, 13, row+10+i);
				   }
			   }
		   }
		   
		   // compensation for business trip
		   BigDecimal[] compens = Mhrmbusinesstrip.getCompensation(bp.getC_BPartner_ID(), dateFrom);
		   for (int k=0; k < compens.length; k++){
			   if (compens[k].doubleValue() != 0){
				   i++;
				   if (i > 2){
					   // shift the basement
					   sheet.insertRow(row+10 + i);
					   cntRow++;
				   }
				   
				   label = new Label(cellStart.getC(), row+10+i, Integer.toString(i+1) + ". Компенсация при командировке", mainStyle);								
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+1, row+10+i, month + " " + targetDate.getYear() + " г.", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+2, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+3, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   number = new Number(cellStart.getC()+4, row+10+i, compens[k].doubleValue(), numberStyle);
				   sheet.addCell(number);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(cellStart.getC()+5, row+10+i).getContents()+" ", mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(6, row+10+i).getContents(), mainStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+9, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(cellStart.getC()+11, row+10+i, " ", borderStyle);
				   sheet.addCell(label);
				   sheet.mergeCells(6, row+10+i, 9, row+10+i);
				   sheet.mergeCells(10, row+10+i, 11, row+10+i);
				   sheet.mergeCells(12, row+10+i, 13, row+10+i);
			   }
			   
		   }
			
		   // Overtime
		   Mhrmovertimeline[] mhrmotline = Mhrmovertimeline.getOvertimeArray(bp.getC_BPartner_ID(), dateFrom);		   
		   double hh = 0;
		   double ss = 0;
		   for (Mhrmovertimeline mm: mhrmotline){
			   hh = hh + mm.gethrm_dayhours().doubleValue() + mm.gethrm_nighthours().doubleValue();
			   ss = ss + mm.gethrm_daytotal().doubleValue() + mm.gethrm_nighttotal().doubleValue();							
		   }
		   if (mhrmotline.length != 0){						
			   i++; 
			   if (i > 2){
				   // shift the basement
				   sheet.insertRow(row+10 + i);
				   cntRow++;
			   }
			   label = new Label(cellStart.getC(), row+10+i, Integer.toString(i+1) + ". Сверхурочные ", mainStyle);								
			   sheet.addCell(label);
			   label = new Label(cellStart.getC()+2, row+10+i, " ", borderStyle);
			   sheet.addCell(label); 
			   number = new Number(cellStart.getC()+3, row+10+i, hh, borderStyle);
			   sheet.addCell(number);
			   number = new Number(cellStart.getC()+4, row+10+i, ss, numberStyle);
			   sheet.addCell(number);
			   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(cellStart.getC()+5, row+10+i).getContents()+" ", mainStyle);
			   sheet.addCell(label);
			   label = new Label(cellStart.getC()+5, row+10+i, sheet.getCell(6, row+10+i).getContents(), mainStyle);
			   sheet.addCell(label);
			   label = new Label(cellStart.getC()+9, row+10+i, " ", borderStyle);
			   sheet.addCell(label);
			   label = new Label(cellStart.getC()+11, row+10+i, " ", borderStyle);
			   sheet.addCell(label);
			   sheet.mergeCells(6, row+10+i, 9, row+10+i);
			   sheet.mergeCells(10, row+10+i, 11, row+10+i);
			   sheet.mergeCells(12, row+10+i, 13, row+10+i);
		   }		  
		   
		   if(i>1){
			   // Всего начислено
			   formula = new Formula(5, row+12+i, "SUM(F"+(row+11)+":F"+(row+12+i)+")", numberStyle);
			   sheet.addCell(formula);
			   //Всего удержано
			   sheet.mergeCells(6, row+12+i, 11, row+12+i);
			   sheet.mergeCells(12, row+12+i, 13, row+12+i);
			   formula = new Formula(12, row+12+i, "SUM(M"+(row+11)+":N"+(row+12+i)+")", numberStyle);
			   sheet.addCell(formula);
		   }else{
			   //Всего удержано					
			   sheet.mergeCells(6, row+14, 11, row+14);
			   sheet.mergeCells(12, row+14, 13, row+14);
			   formula = new Formula(12, row+14, "SUM(M"+(row+11)+":N"+(row+14)+")", numberStyle);
			   sheet.addCell(formula);
			   // Всего начислено
			   formula = new Formula(5, row+14, "SUM(F"+(row+11)+":F"+(row+14)+")", numberStyle);
			   sheet.addCell(formula);
		   }
		   
		   MAcctSchema as = MAcctSchema.get(m_ctx, Env.getContextAsInt(m_ctx, "$C_AcctSchema_ID"));
		   PaymentAmount[] payments = getSaldo(X_C_Payment.Table_ID, getAccountSalary(as, Doc.ACCTTYPE_HRMGo).getAccount_ID(),bp.getC_BPartner_ID(),dateFrom);
		   MPayment payment = null;
		   
		   int crwi = i;
		   int cr = i;
		   if(i>1){

			   //3. Выплачено
			   sheet.mergeCells(6, row+13+i, 11, row+13+i);
			   sheet.mergeCells(12, row+13+i, 13, row+13+i);
			   
			   for(PaymentAmount pa: payments){
				   i++;
				   if(i > 2){
					   // shift the basement
					   sheet.insertRow(row+14+i);
					   cntRow++;
				   }
				   if(pa.getPaymentID() != 0)
					   payment = new MPayment(m_ctx, pa.getPaymentID(), get_TrxName());
				   String textPayment = "";
				   if(payment != null)
					   textPayment = "Платежное поручение № "+payment.getDocumentNo();
				   //
				   label = new Label(1, row+14+i, " ", borderStyle);								
				   sheet.addCell(label);
				   label = new Label(2, row+14+i, " ", borderStyle);
				   sheet.addCell(label); 
				   label = new Label(3, row+14+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(4, row+14+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(5, row+14+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(6, row+14+i, textPayment, mainStyle);
				   sheet.addCell(label);
				   number = new Number(12, row+14+i, pa.getAmtacctcr().doubleValue(), numberStyle);
				   sheet.addCell(number);
				   
				   // Сумма перечисленная в банк
				   sheet.mergeCells(6, row+14+i, 11, row+14+i);
				   sheet.mergeCells(12, row+14+i, 13, row+14+i);
			   }
			   
			   //Всего выплат
			   sheet.mergeCells(6, row+16+i, 11, row+16+i);
			   sheet.mergeCells(12, row+16+i, 13, row+16+i);
			   formula = new Formula(12, row+16+i, "SUM(M"+(row+13+i)+":N"+(row+16+i)+")", numberStyle);
			   sheet.addCell(formula);
			   //Долг за организацией на конец месяца
			   sheet.mergeCells(6, row+17+i, 11, row+17+i);
			   sheet.mergeCells(12, row+17+i, 13, row+17+i);
			   if(m > 0)
				   formula = new Formula(12, row+17+i, "F"+(row+13+crwi)+"-M"+(row+13+crwi)+"-M"+(row+17+i)+"+M"+(row-2), numberStyle);
			   else
				   formula = new Formula(12, row+17+i, "F"+(row+13+crwi)+"-M"+(row+13+crwi)+"-M"+(row+17+i), numberStyle);
				   sheet.addCell(formula);
		   }else{
			  
			   //
			   if(i <= 1){
				   // ОПВ исчисленный
				   sheet.mergeCells(6, row+11+i, 9, row+11+i);
				   sheet.mergeCells(10, row+11+i, 11, row+11+i);
				   sheet.mergeCells(12, row+11+i, 13, row+11+i);
			   }
			   
			   if(i <= 1) {
				   i = 2;
				   crwi = 1;
			   }

			   //3. Выплачено							
			   sheet.mergeCells(6, row+15, 11, row+15);
			   sheet.mergeCells(12, row+15, 13, row+15);
			   
			   for(PaymentAmount pa: payments){
				   i++;
				   if (i > 2){
					   // shift the basement
					   sheet.insertRow(row+15+i);
					   cntRow++;
				   }
				   if(pa.getPaymentID() != 0)
					   payment = new MPayment(m_ctx, pa.getPaymentID(), get_TrxName());
				   String textPayment = "";
				   if(payment != null)
					   textPayment = "Платежное поручение № "+payment.getDocumentNo();
				   //
				   label = new Label(1, row+15+i, " ", borderStyle);								
				   sheet.addCell(label);
				   label = new Label(2, row+15+i, " ", borderStyle);
				   sheet.addCell(label); 
				   label = new Label(3, row+15+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(4, row+15+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(5, row+15+i, " ", borderStyle);
				   sheet.addCell(label);
				   label = new Label(6, row+15+i, textPayment, mainStyle);
				   sheet.addCell(label);
				   number = new Number(12, row+15+i, pa.getAmtacctcr().doubleValue(), numberStyle);
				   sheet.addCell(number);
				   
				   // Сумма перечисленная в банк
				   sheet.mergeCells(6, row+15+i, 11, row+15+i);
				   sheet.mergeCells(12, row+15+i, 13, row+15+i);
			   }

			   //Всего выплат
			   sheet.mergeCells(6, row+16+i, 11, row+16+i);
			   sheet.mergeCells(12, row+16+i, 13, row+16+i);
			   formula = new Formula(12, row+16+i, "SUM(M"+(row+16+crwi)+":N"+(row+16+i)+")", numberStyle);
			   sheet.addCell(formula);
			   //Долг за организацией на конец месяца
			   sheet.mergeCells(6, row+17+i, 11, row+17+i);
			   sheet.mergeCells(12, row+17+i, 13, row+17+i);
			   if(m > 0)
				   formula = new Formula(12, row+17+i, "F"+(row+14+crwi)+"-M"+(row+14+crwi)+"-M"+(row+17+i)+"+M"+(row-2), numberStyle);
			   else
				   formula = new Formula(12, row+17+i, "F"+(row+14+crwi)+"-M"+(row+14+crwi)+"-M"+(row+17+i), numberStyle);
			   sheet.addCell(formula);
		   }

		   // Formulas
		   // ИПН исчисленный
		   label = new Label(10, row+10, month + " " + targetDate.getYear() + " г.", borderStyle);
		   sheet.addCell(label);
		   number = new Number(12, row+10, getEmployeeSum(bp.getC_BPartner_ID(), dateFrom, "IPN").doubleValue(), numberStyle);
		   sheet.addCell(number);
		   // ОПВ исчисленный
		   label = new Label(10, row+11, month + " " + targetDate.getYear() + " г.", borderStyle);
		   sheet.addCell(label);
		   number = new Number(12, row+11, getEmployeeSum(bp.getC_BPartner_ID(), dateFrom, "OPV").doubleValue(), numberStyle);
		   sheet.addCell(number);
		   
		   // Доп удержание кроме налогов
		   int cc = 0;
		   // Медстрахование
		   double medAmount = getEmployeeSum(bp.getC_BPartner_ID(), dateFrom, "MEDICARE_AMOUNT").doubleValue();
		   if(medAmount != 0){
			   label = new Label(6, row+12+cc, "Медстрахование", mainStyle);
			   sheet.addCell(label);
			   label = new Label(10, row+12+cc, month + " " + targetDate.getYear() + " г.", borderStyle);
			   sheet.addCell(label);
			   number = new Number(12, row+12+cc, medAmount, numberStyle);
			   sheet.addCell(number);
			   cc++;
		   }
		   // Внутреннее удержание (Благотворительность)
		   double interAmount = Mhrmintercheckoff.getAmountType(bp.getC_BPartner_ID(), dateFrom, X_hrm_intercheckoff.HRM_DEDUCTIONTYPE_CHARITY).doubleValue();
		   if(interAmount != 0){
			   if(cc >= 1) 
				   crwi = cc;
			   else
				   crwi = 0;
			   label = new Label(6, row+12+cc, MRefList.getListName(m_ctx, X_hrm_intercheckoff.HRM_DEDUCTIONTYPE_AD_Reference_ID, X_hrm_intercheckoff.HRM_DEDUCTIONTYPE_CHARITY), mainStyle);
			   sheet.addCell(label);
			   label = new Label(10, row+12+cc, month + " " + targetDate.getYear() + " г.", borderStyle);
			   sheet.addCell(label);
			   number = new Number(12, row+12+cc, interAmount, numberStyle);
			   sheet.addCell(number);
			   
			   if(cr < 2){
				   sheet.mergeCells(6, row+12+cc, 9, row+12+cc);
				   sheet.mergeCells(10, row+12+cc, 11, row+12+cc);
				   sheet.mergeCells(12, row+12+cc, 13, row+12+cc);
			   }

			   cc++;
		   }
		   // Дополнительный доход
		   double profitAmount = getEmployeeSum(bp.getC_BPartner_ID(), dateFrom, "PROFIT_AMOUNT").doubleValue();
		   if(profitAmount != 0){
			   if(cc >= 1) 
				   crwi = cc;
			   else
				   crwi = 0;
			   label = new Label(6, row+12+cc, "Доп. доход", mainStyle);
			   sheet.addCell(label);
			   label = new Label(10, row+12+cc, month + " " + targetDate.getYear() + " г.", borderStyle);
			   sheet.addCell(label);
			   number = new Number(12, row+12+cc, profitAmount, numberStyle);
			   sheet.addCell(number);
			   
			   if(cr < 2){
				   sheet.mergeCells(6, row+12+cc, 9, row+12+cc);
				   sheet.mergeCells(10, row+12+cc, 11, row+12+cc);
				   sheet.mergeCells(12, row+12+cc, 13, row+12+cc);
			   }
			   
			   cc++;
		   }
		   // Займы
		   double loanprofitAmount = getEmployeeSum(bp.getC_BPartner_ID(), dateFrom, "LOAN").doubleValue();
		   if(loanprofitAmount != 0){
			   if(cc >= 1) 
				   crwi = cc;
			   else
				   crwi = 0;
			   label = new Label(6, row+12+cc, "Займ", mainStyle);
			   sheet.addCell(label);
			   label = new Label(10, row+12+cc, month + " " + targetDate.getYear() + " г.", borderStyle);
			   sheet.addCell(label);
			   number = new Number(12, row+12+cc, loanprofitAmount, numberStyle);
			   sheet.addCell(number);
			   
			   if(cr < 2){
				   sheet.mergeCells(6, row+12+cc, 9, row+12+cc);
				   sheet.mergeCells(10, row+12+cc, 11, row+12+cc);
				   sheet.mergeCells(12, row+12+cc, 13, row+12+cc);
			   }
			   
			   cc++;
		   }
		   
		   // Долг за организацией на начало месяца
		   if(m > 0){
			   if(i < 2)
				   formula = new Formula(5, row+19, "M"+(row-2), numberStyle);
			   else
				   formula = new Formula(5, row+17+i, "M"+(row-2), numberStyle);
			   sheet.addCell(formula);
		   }
		   		   
		   //
		   calendar.add(Calendar.MONTH, 1);
		   calendar.add(Calendar.DAY_OF_MONTH, 5);
	   }
	   
	   copy.write();
	   copy.close();
	   tableBook.close();
	   samCopy.close();
	   sample.close();
	   sampleCopy.delete();
	   temporaryFile.delete();
	   temporarySample.delete();
	   
	   // Open the file with the application associated
	   String fileName = fullPath.toString();
	   Util.launchFile(new File(fileName));
	       
		return Msg.translate(m_ctx, "Success");
	}
	
	private static void copyTo(WritableSheet sheetFrom, WritableSheet sheetTo, ExcelCell capS, ExcelCell capE, int row) throws RowsExceededException, WriteException{
		
		Label label;
		for (int i = capS.getR(); i < capE.getR()+1; i++){
			for (int j = capS.getC()+1 ; j < capE.getC() ; j++){
				WritableCell readCell = sheetFrom.getWritableCell(j, i);
				CellFormat readFormat = readCell.getCellFormat();
				label = new Label(j,row, readCell.getContents(),readFormat);
				sheetTo.addCell(label);
			}
			row++;
		}
		
	}
	
	/**
	 * @param C_BPartner_ID
	 * @param dt
	 * @param type (OPV,IPN)
	 * @return
	 */
	private BigDecimal getSumTax(int C_BPartner_ID, Timestamp dt, String column){
		
		if(C_BPartner_ID == 0)
    		return null;
		
		BigDecimal result = new BigDecimal(0);		
		StringBuffer sql = new StringBuffer();
		
		if(column.isEmpty() || column == null)
			return result;		
		
		if (column == "HRM_TOTAL"){
			sql.append(" SELECT coalesce(ROUND(SUM(HRM_OPV * 10),2),0) FROM HRM_PAYMENT_LIST \n ");			
		}else{
			sql.append(" SELECT coalesce(ROUND(SUM(" + column + "),2),0) FROM HRM_SALARYSUM_V \n ");
		}
		
		sql.append(" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
				   " AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
				   " AND DATEFROM BETWEEN DATE_TRUNC('YEAR'," + DB.TO_DATE(dt, true) + ") \n " +
				   " AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL");
		
//		if (dt.getTime() < CONST_DATE.getMillis()){
//			sql.append(" AND DATEFROM BETWEEN '2012-01-01' \n ");					   					  
//		}else if (dt.getTime() > CONST_YEAR.getMillis()){
//			sql.append(" AND DATEFROM BETWEEN DATE_TRUNC('YEAR'," + DB.TO_DATE(dt, true) + ") \n ");
//		}else{
//			sql.append(" AND DATEFROM BETWEEN '2012-10-01' \n ");
//		}
//		sql.append(" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL");
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
	try
		{
			pstmt = DB.prepareStatement(sql.toString(),null);			
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getBigDecimal(1);			
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}
	
	/** Load Model for HRM Salary Employee */
	public static BigDecimal getEmployeeSum(int C_BPartner_ID, Timestamp dt, String column){
		
		if(C_BPartner_ID == 0)
    		return null;
		
		BigDecimal result = new BigDecimal(0);
		StringBuffer sql = new StringBuffer();		
		
		if(column.isEmpty() || column == null)
			return result;
		
		sql.append("SELECT coalesce(ROUND(SUM(" + column + "),2),0) FROM ");
		
		if (column == "HRM_TOTAL"){
			sql.append(" HRM_PAYMENT_LIST \n ");
		}else{
			sql.append(" HRM_SALARYSUM_V \n ");
		}
		
		sql.append(" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
				   " AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
				   " AND DATE_TRUNC('MONTH',DATEFROM) <=" + DB.TO_DATE(dt) + " \n " +
				   " AND DATE_TRUNC('MONTH',DATEFROM) + '1 MON'::INTERVAL - '1 DAY'::INTERVAL >=" + DB.TO_DATE(dt));		
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(),null);						
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getBigDecimal(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}
	
	/** accruals from the beginning of the year */
	public static BigDecimal getEmployeeSumTotal(int C_BPartner_ID, Timestamp dt){
		
		if(C_BPartner_ID == 0)
    		return null;
		
		BigDecimal result = new BigDecimal(0);
		StringBuffer sql = new StringBuffer();		
		
		sql.append("SELECT coalesce(ROUND(SUM(TOTALSUM),2),0) FROM \n");
		sql.append(" HRM_SALARYSUM_V \n ");
		sql.append(" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
				   " AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
				   " AND DATEFROM  BETWEEN DATE_TRUNC('YEAR'," + DB.TO_DATE(dt) + ") AND " + DB.TO_DATE(dt));		
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(),null);						
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getBigDecimal(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}
	
	public static MAccount getAccountSalary (MAcctSchema as, int acctType){
		String account = "3350%";
		if(Doc.ACCTTYPE_HRMGo == acctType)
			account = "1030%";  // деньги в пути
			
		
		String sql = "select c.c_validcombination_id \n" +
				"from c_validcombination c \n" +
				"inner join c_elementvalue e on e.c_elementvalue_id = c.account_id \n " +
				"where value like '"+account+"'";
		
		int C_ValidCombination_ID = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if (rs.next())
				C_ValidCombination_ID = rs.getInt(1);
		}
		catch (SQLException e)
		{
			CLogger.get().log(Level.SEVERE, "SQL=" + sql, e);
		}
		finally {
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		if (C_ValidCombination_ID == 0)
			return null;
		//	Return Account
		MAccount acct = MAccount.get (as.getCtx(), C_ValidCombination_ID);
		return acct;
	}	//	getAccount
	
	private PaymentAmount[] getSaldo(int tableID, int accountID, int C_BPartner_ID, Timestamp dateacct) {
		
		ArrayList<PaymentAmount> list = new ArrayList<PaymentAmount>();	
		PaymentAmount result = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = " select fa.ad_org_id \n"
                    +", fa.ad_client_id \n"
                    +", fa.account_id \n"
                    +", fa.c_bpartner_id \n"
                    +", fa.record_id \n" 
                    +", sum(fa.amtacctdr) as amtacctdr \n"
                    +", sum(fa.amtacctcr) as amtacctcr \n"
                    +", sum(fa.amtacctcr) - sum(fa.amtacctdr) as saldo \n"
                    +" from fact_acct fa \n"
                    +" where fa.account_id="+accountID + "\n" +
                    "  and fa.ad_table_id = "+tableID +" \n"  					// Payment
                    +" and fa.record_id in(select distinct t.c_payment_id " +
                    "						from fi_c_paymentLine t join c_payment r on t.c_payment_id=r.c_payment_id " +
                    "						where c_docType_id=" + MDocType.getDocType(X_C_DocType.DOCBASETYPE_APSalary) + ")" 
                    +" and fa.c_bpartner_id =" + C_BPartner_ID + " \n"
                    +" and fa.dateacct between DATE_TRUNC('MONTH',"+DB.TO_DATE(dateacct, true)+") " +
                    		"and (DATE_TRUNC('MONTH',"+DB.TO_DATE(dateacct, true)+") + INTERVAL '1 MONTH' - INTERVAL '1 DAY')::DATE \n"
                    +"group by fa.ad_org_id \n"
                    +"       , fa.ad_client_id \n"
                    +"       , fa.account_id \n"
                    +"       , fa.c_bpartner_id \n"
                    +"        , fa.record_id " ;
		try{
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				result = new PaymentAmount(rs.getInt("record_id"),rs.getBigDecimal("amtacctcr"));
				list.add(result);
			}
		}catch(SQLException e){
			log.log(Level.WARNING, e.getLocalizedMessage());
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return list.toArray(new PaymentAmount[list.size()]);
	}
	
	public class PaymentAmount{
		
		private int paymentID;
		private BigDecimal amtacctcr;
		
		PaymentAmount(int payment, BigDecimal amtcr){
			this.paymentID = payment;
			this.amtacctcr = amtcr;
		}
		
		public int getPaymentID() {
			return paymentID;
		}
		public BigDecimal getAmtacctcr() {
			return amtacctcr;
		}
		
	}
	
}
